package com.web.expspringboot.mapper;


import com.web.expspringboot.entity.ProductSt;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ProductMapper {

    // 发布商品
    // 不需要指定主键，在数据库中设置了自增
    @Insert("INSERT INTO product_st " +
            "(product_title," +
            " product_intro," +
            " product_price," +
            " product_imgs," +
            " publish_time," +
            " owner_id," +
            " location, status) " +
            "VALUES (#{productTitle}," +
            " #{productIntro}," +
            " #{productPrice}," +
            " #{productImgs}," +
            " CURRENT_TIMESTAMP," +
            " #{ownerId}," +
            " #{location}," +
            " 1)")
    int insertProduct(ProductSt product);

    // 删除商品
    @Delete("DELETE FROM product_st WHERE product_id = #{productId}")
    int deleteProduct(@Param("productId") long productId);

    // 修改商品
    @Update("UPDATE product_st SET " +
            "product_title = #{productTitle}, " +
            "product_intro = #{productIntro}, " +
            "product_price = #{productPrice}, " +
            "product_imgs = #{productImgs}, " +
            "location = #{location}, " +
            "status = #{status} " +
            "WHERE product_id = #{productId}"
            + " ORDER BY publish_time DESC"
    )
    int updateProduct(ProductSt product);

    // 查询商品
    @Select("SELECT " +
            "product_id AS productId, " +
            "product_title AS productTitle, " +
            "product_intro AS productIntro, " +
            "product_price AS productPrice, " +
            "product_imgs AS productImgs, " +
            "publish_time AS publishTime, " +
            "owner_id AS ownerId, " +
            "location AS location, " +
            "status AS status " +
            "FROM product_st WHERE product_id = #{productId}"
    )
    ProductSt selectProductById(@Param("productId") long productId);

    // 查询所有商品，按最新到最老排序
    @Select("SELECT " +
            "product_id AS productId, " +
            "product_title AS productTitle, " +
            "product_intro AS productIntro, " +
            "product_price AS productPrice, " +
            "product_imgs AS productImgs, " +
            "publish_time AS publishTime, " +
            "owner_id AS ownerId, " +
            "location AS location, " +
            "status AS status " +
            "FROM product_st"
            + " ORDER BY publish_time DESC " +
            "LIMIT #{length} OFFSET #{startIndex}")
    List<ProductSt> selectAllProducts(int startIndex, int length);

    // 修改商品状态
    @Update("UPDATE product_st SET status = #{status} WHERE product_id = #{productId}")
    boolean updateProductStatus(@Param("productId") long productId, @Param("status") int status);


    // 查询某一用户发布的所有商品
    @Select("SELECT " +
            "product_id AS productId, " +
            "product_title AS productTitle, " +
            "product_intro AS productIntro, " +
            "product_price AS productPrice, " +
            "product_imgs AS productImgs, " +
            "publish_time AS publishTime, " +
            "owner_id AS ownerId, " +
            "location AS location, " +
            "status AS status " +
            "FROM product_st WHERE owner_id = #{ownerId}"
            + " ORDER BY publish_time DESC "+
            "LIMIT #{length} OFFSET #{startIndex}")
    List<ProductSt> selectProductsByOwnerId(@Param("ownerId") long ownerId, @Param("startIndex") int startIndex, @Param("length") int length);

    // 查询某一时间段内发布的所有商品
    @Select("SELECT " +
            "product_id AS productId, " +
            "product_title AS productTitle, " +
            "product_intro AS productIntro, " +
            "product_price AS productPrice, " +
            "product_imgs AS productImgs, " +
            "publish_time AS publishTime, " +
            "owner_id AS ownerId, " +
            "location AS location, " +
            "status AS status " +
            "FROM product_st WHERE publish_time BETWEEN #{startTime} AND #{endTime}"
            + " ORDER BY publish_time DESC"
    )
    List<ProductSt> selectProductsByTime(@Param("startTime") String startTime, @Param("endTime") String endTime);

}
